package db;

import java.sql.*;
import java.util.ArrayList;

import model.*;
import util.QueryBuilder;

public class DBReservation_ApartmentType implements IFDBReservation_ApartmentType
{
	/* FIELDS */
	private Connection con;
	private QueryBuilder qb;

	/* CONSTRUCTORS */
	public DBReservation_ApartmentType()
	{
		con = DBConnection.getInstance().getDBcon();
		qb = new QueryBuilder("Reservation_ApartmentType", new String[]
		/* custom */
		{ "apartmentTypeId", "reservationId"});
		/* end custom */
	}

	/* BUILDERS */
	private ArrayList<Object> getValues(Reservation_ApartmentType item)
	{
		ArrayList<Object> l = new ArrayList<Object>();
		/* custom */
		l.add(item.getApartmentType().getId());
		l.add(item.getReservation().getId());
		/* end custom */
		return l;
	}

	private Reservation_ApartmentType buildObject(ResultSet results)
	{
		Reservation_ApartmentType item = new Reservation_ApartmentType();
		try
		{
			/* custom */
			item.setApartmentType(new ApartmentType(results.getInt("apartmentTypeId")));
			item.setReservation(new Reservation(results.getInt("reservationId")));
			/* end custom */
		} catch (Exception e)
		{
			System.out.println("error in building object");
		}
		return item;
	}

	private Reservation_ApartmentType selectSingle(String condition)
	{
		PreparedStatement ps = null;
		Reservation_ApartmentType item = new Reservation_ApartmentType();
		String select = qb.selectQuery(condition);
		try
		{
			ps = con.prepareStatement(select);
			ResultSet rs = ps.executeQuery();
			while (rs.next())
			{
				item = this.buildObject(rs);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			if (ps != null)
			{
				try
				{
					ps.close();
				} catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}
		return item;
	}

	private ArrayList<Reservation_ApartmentType> selectMultiple(String condition)
	{
		ArrayList<Reservation_ApartmentType> list = new ArrayList<Reservation_ApartmentType>();
		PreparedStatement ps = null;
		String select = qb.selectQuery(condition);
		try
		{
			ps = con.prepareStatement(select);
			ResultSet rs = ps.executeQuery();
			while (rs.next())
			{
				Reservation_ApartmentType item = new Reservation_ApartmentType();
				item = this.buildObject(rs);
				list.add(item);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			if (ps != null)
			{
				try
				{
					ps.close();
				} catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}
		return list;
	}

	@Override
	public ArrayList<Reservation_ApartmentType> getAll()
	{
		return this.selectMultiple("");
	}

	@Override
	public ArrayList<Reservation_ApartmentType> getByType(int apartmentTypeId)
	{
		return this.selectMultiple("where apartmentTypeId="+apartmentTypeId);
	}
/*
	@Override
	public Reservation_ApartmentType getById(int id)
	{
		String condition = "where id=" + id;
		return this.selectSingle(condition);
	}
	*/
/*
	@Override
	public ArrayList<Reservation_ApartmentType> getByName(String name)
	{
		String condition = "where name='" + name + "'";
		return this.selectMultiple(condition);
	}
*/
	/**
	 * Pre: table has no column with name "id"
	 * Post: insert all defined values
	 * @return void
	 */
	@Override
	public void insert(Reservation_ApartmentType item)
	{
		PreparedStatement ps = null;
		try
		{
			ps = con.prepareStatement(qb.insertQuery());
			ArrayList<Object> values = this.getValues(item);
			int i=1;
			for (Object o : values)
			{
				//System.out.println("Preparing object: "+o);
				//System.out.println("Object index: "+i);
				if (i <= values.size())
				{
					qb.setObject(ps, i, o);
				}
				i++;
			}
			ps.executeUpdate();
			
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			if (ps != null)
			{
				try
				{
					ps.close();
				} catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}
	}
	/*
	@Override
	public int insertScope(Reservation_ApartmentType item)
	{
		PreparedStatement ps = null;
		int id = 0;
		try
		{
			ps = con.prepareStatement(qb.insertQuery(), Statement.RETURN_GENERATED_KEYS);
			ArrayList<Object> values = this.getValues(item);
			int i=0;
			for (Object o : values)
			{
				System.out.println("Preparing object: "+o);
				System.out.println("Object index: "+i);
				if (i > 0 && i < values.size())
				{
					qb.setObject(ps, i, o);
				}
				i++;
			}
			int affectedRows = ps.executeUpdate();
			System.out.println(ps.getResultSet());
			if (affectedRows == 0)
			{
				throw new SQLException("Insert failed, no rows affected.");
			}

			try (ResultSet generatedKeys = ps.getGeneratedKeys())
			{
				if (generatedKeys.next())
				{
					id = generatedKeys.getInt(1);
					System.out.println("Generated key:" + generatedKeys.getInt(1));
				} else
				{
					throw new SQLException("Insert, no ID obtained.");
				}
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			if (ps != null)
			{
				try
				{
					ps.close();
				} catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}
		return id;
	}
*/
	/*
	@Override
	public void update(Reservation_ApartmentType item)
	{
		PreparedStatement ps = null;
		try
		{
			con.setAutoCommit(false);
			ps = con.prepareStatement(qb.updateQuery("WHERE id=?"));
			ArrayList<Object> values = this.getValues(item);
			for (Object o : values)
			{
				int i = values.indexOf(o);
				if (i > 0 && i < values.size())
				{
					qb.setObject(ps, i, o);
				} else if (i == 0)
				{
					qb.setObject(ps, values.size(), o);
				}
			}
			ps.executeUpdate();
			con.commit();
		} catch (SQLException e)
		{
			if (con != null)
			{
				try
				{
					System.err.println("Transaction is being rolled back");
					con.rollback();
				} catch (SQLException sqle)
				{
					sqle.printStackTrace();
				}
			}
			e.printStackTrace();
		} finally
		{
			if (ps != null)
			{
				try
				{
					ps.close();
					con.setAutoCommit(true);
				} catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}

	}
*/
	@Override
	public void delete(Reservation_ApartmentType item)
	{
		PreparedStatement ps = null;
		try
		{
			con.setAutoCommit(false);
			ps = con.prepareStatement(qb.deleteQuery("WHERE apartmentTypeId=? AND reservationId=?"));
			ps.setInt(1, item.getApartmentType().getId());
			ps.setInt(2, item.getReservation().getId());
			ps.executeUpdate();
			con.commit();
		} catch (SQLException e)
		{
			if (con != null)
			{
				try
				{
					System.err.print("Transaction is being rolled back");
					con.rollback();
				} catch (SQLException sqle)
				{
					sqle.printStackTrace();
				}
			}
			e.printStackTrace();
		} finally
		{
			if (ps != null)
			{
				try
				{
					ps.close();
					con.setAutoCommit(true);
				} catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}

	}

}
